package zy.dao.sell.ecoupon.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sell.ecoupon.ECouponDAO;
import zy.dto.sell.ecoupon.SellECouponDto;
import zy.entity.sell.ecoupon.T_Sell_ECoupon;
import zy.entity.sell.ecoupon.T_Sell_ECouponList;
import zy.entity.sell.ecoupon.T_Sell_ECoupon_Brand;
import zy.entity.sell.ecoupon.T_Sell_ECoupon_Product;
import zy.entity.sell.ecoupon.T_Sell_ECoupon_Shop;
import zy.entity.sell.ecoupon.T_Sell_ECoupon_Type;
import zy.entity.sell.ecoupon.T_Sell_Ecoupon_User;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class ECouponDAOImpl extends BaseDaoImpl implements ECouponDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ec_state = params.get("ec_state");
		Object ec_manager = params.get("ec_manager");
		Object ec_name = params.get("ec_name");
		Object ec_number = params.get("ec_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_ecoupon t");
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" JOIN t_sell_ecoupon_shop ecs ON ecs_ec_number = ec_number AND ecs.companyid = t.companyid");
		}
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ec_sysdate >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ec_sysdate <= :enddate ");
		}
		if(StringUtil.isNotEmpty(ec_state)){
			sql.append(" AND ec_state = :ec_state ");
		}
		if(StringUtil.isNotEmpty(ec_manager)){
			sql.append(" AND ec_manager = :ec_manager ");
		}
		if(StringUtil.isNotEmpty(ec_name)){
			sql.append(" AND INSTR(ec_name,:ec_name) > 0 ");
		}
		if(StringUtil.isNotEmpty(ec_number)){
			sql.append(" AND INSTR(ec_number,:ec_number) > 0 ");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" AND ecs_shop_code = :shop_code ");
		}else {
			sql.append(" AND ec_shop_code = :shop_code ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Sell_ECoupon> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object ec_state = params.get("ec_state");
		Object ec_manager = params.get("ec_manager");
		Object ec_name = params.get("ec_name");
		Object ec_number = params.get("ec_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ec_id,ec_number,ec_name,ec_manager,ec_remark,ec_deadline,ec_begindate,ec_enddate,ec_shop_code,ec_sysdate,ec_level,");
		sql.append(" ec_mode,ec_use_mode,ec_type,ec_state,t.companyid");
		sql.append(" FROM t_sell_ecoupon t");
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" JOIN t_sell_ecoupon_shop ecs ON ecs_ec_number = ec_number AND ecs.companyid = t.companyid");
		}
		sql.append(" WHERE 1 = 1");
		if(StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND ec_sysdate >= :begindate ");
		}
		if(StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND ec_sysdate <= :enddate ");
		}
		if(StringUtil.isNotEmpty(ec_state)){
			sql.append(" AND ec_state = :ec_state ");
		}
		if(StringUtil.isNotEmpty(ec_manager)){
			sql.append(" AND ec_manager = :ec_manager ");
		}
		if(StringUtil.isNotEmpty(ec_name)){
			sql.append(" AND INSTR(ec_name,:ec_name) > 0 ");
		}
		if(StringUtil.isNotEmpty(ec_number)){
			sql.append(" AND INSTR(ec_number,:ec_number) > 0 ");
		}
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、合伙店
			sql.append(" AND ecs_shop_code = :shop_code ");
		}else {
			sql.append(" AND ec_shop_code = :shop_code ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY ec_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sell_ECoupon.class));
	}
	
	@Override
	public T_Sell_ECoupon load(Integer ec_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ec_id,ec_number,ec_name,ec_manager,ec_remark,ec_deadline,ec_begindate,ec_enddate,ec_shop_code,ec_sysdate,ec_level,");
		sql.append(" ec_mode,ec_use_mode,ec_state,ec_type,companyid");
		sql.append(" FROM t_sell_ecoupon t");
		sql.append(" WHERE ec_id=:ec_id");
		sql.append(" LIMIT 1");
		try{
			T_Sell_ECoupon eCoupon = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ec_id", ec_id),
					new BeanPropertyRowMapper<>(T_Sell_ECoupon.class));
			MapSqlParameterSource parameterSource = new MapSqlParameterSource().addValue("number", eCoupon.getEc_number()).addValue("companyid", eCoupon.getCompanyid());
			//查询活动店铺
			sql.setLength(0);
			sql.append("SELECT GROUP_CONCAT(sp_code) AS shop_codes,GROUP_CONCAT(sp_name) AS shop_names");
			sql.append(" FROM t_sell_ecoupon_shop ecs");
			sql.append(" JOIN t_base_shop sp ON ecs_shop_code = sp_code AND ecs.companyid = sp.companyid");
			sql.append(" WHERE ecs_ec_number = :number");
			sql.append(" AND ecs.companyid = :companyid");
			Map<String, Object> map = namedParameterJdbcTemplate.queryForMap(sql.toString(), parameterSource);
			eCoupon.setShop_codes(StringUtil.trimString(map.get("shop_codes")));
			eCoupon.setShop_names(StringUtil.trimString(map.get("shop_names")));
			//发放条件--1.全场2.品牌3.类别4.商品
			sql.setLength(0);
			if(eCoupon.getEc_type() == 1){
				if(eCoupon.getEc_mode().equals(2)){
					sql.append(" SELECT GROUP_CONCAT(bd_code) AS mode_codes,GROUP_CONCAT(bd_name) AS mode_names");
					sql.append(" FROM t_sell_ecoupon_brand ecb");
					sql.append(" JOIN t_base_brand bd ON ecb_bd_code = bd_code AND ecb.companyid = bd.companyid");
					sql.append(" WHERE ecb_type = 1");
					sql.append(" AND ecb_ec_number = :number");
					sql.append(" AND ecb.companyid = :companyid");
					map = namedParameterJdbcTemplate.queryForMap(sql.toString(), parameterSource);
					eCoupon.setMode_codes(StringUtil.trimString(map.get("mode_codes")));
					eCoupon.setMode_names(StringUtil.trimString(map.get("mode_names")));
				}else if(eCoupon.getEc_mode().equals(3)){
					sql.append(" SELECT GROUP_CONCAT(tp_code) AS mode_codes,GROUP_CONCAT(tp_name) AS mode_names");
					sql.append(" FROM t_sell_ecoupon_type ect");
					sql.append(" JOIN t_base_type tp ON ect_tp_code = tp_code AND ect.companyid = tp.companyid");
					sql.append(" WHERE ect_type = 1");
					sql.append(" AND ect_ec_number = :number");
					sql.append(" AND ect.companyid = :companyid");
					map = namedParameterJdbcTemplate.queryForMap(sql.toString(), parameterSource);
					eCoupon.setMode_codes(StringUtil.trimString(map.get("mode_codes")));
					eCoupon.setMode_names(StringUtil.trimString(map.get("mode_names")));
				}else if(eCoupon.getEc_mode().equals(4)){
					sql.append("SELECT GROUP_CONCAT(pd_code) AS mode_codes,GROUP_CONCAT(pd_name) AS mode_names");
					sql.append(" FROM t_sell_ecoupon_product ecp");
					sql.append(" JOIN t_base_product pd ON ecp_pd_code = pd_code AND ecp.companyid = pd.companyid");
					sql.append(" WHERE ecp_type = 1");
					sql.append(" AND ecp_ec_number = :number");
					sql.append(" AND ecp.companyid = :companyid");
					map = namedParameterJdbcTemplate.queryForMap(sql.toString(), parameterSource);
					eCoupon.setMode_codes(StringUtil.trimString(map.get("mode_codes")));
					eCoupon.setMode_names(StringUtil.trimString(map.get("mode_names")));
				}
			}
			//使用范围--1.全场2.品牌3.类别4.商品
			sql.setLength(0);
			if(eCoupon.getEc_use_mode().equals(2)){
				sql.append(" SELECT GROUP_CONCAT(bd_code) AS mode_codes,GROUP_CONCAT(bd_name) AS mode_names");
				sql.append(" FROM t_sell_ecoupon_brand ecb");
				sql.append(" JOIN t_base_brand bd ON ecb_bd_code = bd_code AND ecb.companyid = bd.companyid");
				sql.append(" WHERE ecb_type = 2");
				sql.append(" AND ecb_ec_number = :number");
				sql.append(" AND ecb.companyid = :companyid");
				map = namedParameterJdbcTemplate.queryForMap(sql.toString(), parameterSource);
				eCoupon.setUse_mode_codes(StringUtil.trimString(map.get("mode_codes")));
				eCoupon.setUse_mode_names(StringUtil.trimString(map.get("mode_names")));
			}else if(eCoupon.getEc_use_mode().equals(3)){
				sql.append(" SELECT GROUP_CONCAT(tp_code) AS mode_codes,GROUP_CONCAT(tp_name) AS mode_names");
				sql.append(" FROM t_sell_ecoupon_type ect");
				sql.append(" JOIN t_base_type tp ON ect_tp_code = tp_code AND ect.companyid = tp.companyid");
				sql.append(" WHERE ect_type = 2");
				sql.append(" AND ect_ec_number = :number");
				sql.append(" AND ect.companyid = :companyid");
				map = namedParameterJdbcTemplate.queryForMap(sql.toString(), parameterSource);
				eCoupon.setUse_mode_codes(StringUtil.trimString(map.get("mode_codes")));
				eCoupon.setUse_mode_names(StringUtil.trimString(map.get("mode_names")));
			}else if(eCoupon.getEc_use_mode().equals(4)){
				sql.append("SELECT GROUP_CONCAT(pd_code) AS mode_codes,GROUP_CONCAT(pd_name) AS mode_names");
				sql.append(" FROM t_sell_ecoupon_product ecp");
				sql.append(" JOIN t_base_product pd ON ecp_pd_code = pd_code AND ecp.companyid = pd.companyid");
				sql.append(" WHERE ecp_type = 2");
				sql.append(" AND ecp_ec_number = :number");
				sql.append(" AND ecp.companyid = :companyid");
				map = namedParameterJdbcTemplate.queryForMap(sql.toString(), parameterSource);
				eCoupon.setUse_mode_codes(StringUtil.trimString(map.get("mode_codes")));
				eCoupon.setUse_mode_names(StringUtil.trimString(map.get("mode_names")));
			}
			return eCoupon;
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public List<T_Sell_ECouponList> listDetail(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecl_id,ecl_number,ecl_code,ecl_usedmoney,ecl_money,ecl_limitmoney,ecl_amount,ecl_remark,companyid");
		sql.append(" FROM t_sell_ecouponlist");
		sql.append(" WHERE ecl_number = :ecl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" ORDER BY ecl_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("ecl_number", number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
	}

	@Override
	public List<T_Sell_ECouponList> temp(Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecl_id,ecl_number,ecl_usedmoney,ecl_money,ecl_limitmoney,ecl_amount,ecl_remark,ecl_us_id,companyid");
		sql.append(" FROM t_sell_ecouponlist_temp");
		sql.append(" WHERE ecl_us_id = :ecl_us_id");
		sql.append(" AND companyid = :companyid");
		sql.append(" ORDER BY ecl_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("ecl_us_id", us_id).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
	}
	
	@Override
	public T_Sell_ECouponList temp_load(Integer ecl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecl_id,ecl_number,ecl_usedmoney,ecl_money,ecl_limitmoney,ecl_amount,ecl_remark,ecl_us_id,companyid ");
		sql.append(" FROM t_sell_ecouponlist_temp t");
		sql.append(" WHERE ecl_id=:ecl_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ecl_id", ecl_id),
					new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void temp_save(T_Sell_ECouponList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_ecouponlist_temp");
		sql.append(" (ecl_usedmoney,ecl_money,ecl_limitmoney,ecl_amount,ecl_remark,ecl_us_id,companyid)");
		sql.append(" VALUES(:ecl_usedmoney,:ecl_money,:ecl_limitmoney,:ecl_amount,:ecl_remark,:ecl_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp),holder);
		temp.setEcl_id(holder.getKey().intValue());
	}
	
	@Override
	public void temp_save(List<T_Sell_ECouponList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_ecouponlist_temp");
		sql.append(" (ecl_usedmoney,ecl_money,ecl_limitmoney,ecl_amount,ecl_remark,ecl_us_id,companyid)");
		sql.append(" VALUES(:ecl_usedmoney,:ecl_money,:ecl_limitmoney,:ecl_amount,:ecl_remark,:ecl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_update(T_Sell_ECouponList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_ecouponlist_temp");
		sql.append(" SET ecl_usedmoney=:ecl_usedmoney");
		sql.append(" ,ecl_money=:ecl_money");
		sql.append(" ,ecl_limitmoney=:ecl_limitmoney");
		sql.append(" ,ecl_amount=:ecl_amount");
		sql.append(" ,ecl_remark=:ecl_remark");
		sql.append(" WHERE ecl_id=:ecl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_del(Integer ecl_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_ecouponlist_temp");
		sql.append(" WHERE ecl_id=:ecl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ecl_id", ecl_id));
	}
	
	@Override
	public void temp_del(Integer us_id, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_ecouponlist_temp");
		sql.append(" WHERE ecl_us_id = :ecl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ecl_us_id", us_id).addValue("companyid", companyid));
	}

	@Override
	public void save(T_Sell_ECoupon eCoupon) {
		String prefix = CommonUtil.NUMBER_PREFIX_ECOUPON + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(ec_number))) AS new_number");
		sql.append(" FROM t_sell_ecoupon");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(ec_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", eCoupon.getCompanyid()), String.class);
		eCoupon.setEc_number(new_number);
		sql.setLength(0);
		sql.append("INSERT INTO t_sell_ecoupon");
		sql.append(" (ec_number,ec_name,ec_manager,ec_remark,ec_deadline,ec_begindate,ec_enddate,ec_shop_code,ec_sysdate,");
		sql.append(" ec_level,ec_mode,ec_use_mode,ec_state,ec_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ec_number,:ec_name,:ec_manager,:ec_remark,:ec_deadline,:ec_begindate,:ec_enddate,:ec_shop_code,:ec_sysdate,");
		sql.append(" :ec_level,:ec_mode,:ec_use_mode,:ec_state,:ec_type,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(eCoupon),holder);
		eCoupon.setEc_id(holder.getKey().intValue());
	}
	
	@Override
	public void update(T_Sell_ECoupon eCoupon) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_ecoupon");
		sql.append(" SET ec_name=:ec_name");
		sql.append(" ,ec_manager=:ec_manager");
		sql.append(" ,ec_remark=:ec_remark");
		sql.append(" ,ec_deadline=:ec_deadline");
		sql.append(" ,ec_begindate=:ec_begindate");
		sql.append(" ,ec_enddate=:ec_enddate");
		sql.append(" ,ec_sysdate=:ec_sysdate");
		sql.append(" ,ec_level=:ec_level");
		sql.append(" ,ec_mode=:ec_mode");
		sql.append(" ,ec_use_mode=:ec_use_mode");
		sql.append(" ,ec_state=:ec_state");
		sql.append(" ,ec_type=:ec_type");
		sql.append(" WHERE ec_id=:ec_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(eCoupon));
	}
	
	@Override
	public void saveList(List<T_Sell_ECouponList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_ecouponlist");
		sql.append(" (ecl_number,ecl_code,ecl_usedmoney,ecl_money,ecl_limitmoney,ecl_amount,ecl_remark,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ecl_number,:ecl_code,:ecl_usedmoney,:ecl_money,:ecl_limitmoney,:ecl_amount,:ecl_remark,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}

	@Override
	public void saveShop(List<T_Sell_ECoupon_Shop> shops) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_ecoupon_shop");
		sql.append(" (ecs_ec_number,ecs_shop_code,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ecs_ec_number,:ecs_shop_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(shops.toArray()));
	}

	@Override
	public void saveBrand(List<T_Sell_ECoupon_Brand> brands) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_ecoupon_brand");
		sql.append(" (ecb_ec_number,ecb_bd_code,ecb_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ecb_ec_number,:ecb_bd_code,:ecb_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(brands.toArray()));
	}

	@Override
	public void saveProduct(List<T_Sell_ECoupon_Product> products) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_ecoupon_product");
		sql.append(" (ecp_ec_number,ecp_pd_code,ecp_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ecp_ec_number,:ecp_pd_code,:ecp_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(products.toArray()));
	}

	@Override
	public void saveType(List<T_Sell_ECoupon_Type> types) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_sell_ecoupon_type");
		sql.append(" (ect_ec_number,ect_tp_code,ect_type,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ect_ec_number,:ect_tp_code,:ect_type,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(types.toArray()));
	}
	
	@Override
	public void deleteList(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_ecouponlist");
		sql.append(" WHERE ecl_number=:ecl_number AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ecl_number", number).addValue("companyid", companyid));
	}

	@Override
	public void deleteShop(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_ecoupon_shop");
		sql.append(" WHERE ecs_ec_number=:ecs_ec_number AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ecs_ec_number", number).addValue("companyid", companyid));
	}

	@Override
	public void deleteBrand(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_ecoupon_brand");
		sql.append(" WHERE ecb_ec_number=:ecb_ec_number AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ecb_ec_number", number).addValue("companyid", companyid));
	}

	@Override
	public void deleteProduct(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_ecoupon_product");
		sql.append(" WHERE ecp_ec_number=:ecp_ec_number AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ecp_ec_number", number).addValue("companyid", companyid));
	}

	@Override
	public void deleteType(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_ecoupon_type");
		sql.append(" WHERE ect_ec_number=:ect_ec_number AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ect_ec_number", number).addValue("companyid", companyid));
	}

	@Override
	public T_Sell_ECoupon check(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ec_id,ec_number,ec_state,companyid");
		sql.append(" FROM t_sell_ecoupon t");
		sql.append(" WHERE ec_number = :ec_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ec_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sell_ECoupon.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Sell_ECoupon check(Integer ec_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ec_id,ec_number,ec_state,companyid");
		sql.append(" FROM t_sell_ecoupon t");
		sql.append(" WHERE ec_id = :ec_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ec_id", ec_id),
					new BeanPropertyRowMapper<>(T_Sell_ECoupon.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public void approve(T_Sell_ECoupon eCoupon) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_ecoupon");
		sql.append(" SET ec_state=:ec_state");
		sql.append(" WHERE ec_id=:ec_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(eCoupon));
	}

	@Override
	public void del(Integer ec_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sell_ecoupon");
		sql.append(" WHERE ec_id=:ec_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ec_id", ec_id));
	}
	
	
	
	@Override
	public Integer user_count(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		Object begin = paramMap.get("begindate");
		Object ecu_state = paramMap.get("ecu_state");
		Object ecu_name = paramMap.get("ecu_name");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1)");
		sql.append(" FROM t_sell_ecoupon_user t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" JOIN t_base_shop sp ON sp_code = ecu_shop_code AND sp.companyid = t.companyid");
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//加盟店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND ecu_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(ecu_state)){
			sql.append(" AND ecu_state=:ecu_state");
		}
		if(StringUtil.isNotEmpty(ecu_name)){
			sql.append(" AND (INSTR(ecu_name,:ecu_name)>0 OR INSTR(ecu_tel,:ecu_name)>0)");
		}
		if(StringUtil.isNotEmpty(begin)){
			sql.append(" AND ecu_enddate BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), paramMap, Integer.class);
	}

	@Override
	public List<T_Sell_Ecoupon_User> user_list(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		Object begin = paramMap.get("begindate");
		Object ecu_state = paramMap.get("ecu_state");
		Object ecu_name = paramMap.get("ecu_name");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ecu_id,ecu_date,ecu_name,ecu_tel,ecu_vip_code,");
		sql.append(" (SELECT vm_cardcode FROM t_vip_member m WHERE vm_code=ecu_vip_code AND m.companyid=t.companyid LIMIT 1) vip_cardcode,");
		sql.append(" ecu_money,ecu_limitmoney,ecu_enddate,ecu_state,");
		sql.append(" ecu_use_number,ecu_use_date,ecu_use_type");
		sql.append(" FROM t_sell_ecoupon_user t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" JOIN t_base_shop sp ON sp_code = ecu_shop_code AND sp.companyid = t.companyid");
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//加盟店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND ecu_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(ecu_state)){
			sql.append(" AND ecu_state=:ecu_state");
		}
		if(StringUtil.isNotEmpty(ecu_name)){
			sql.append(" AND (INSTR(ecu_name,:ecu_name)>0 OR INSTR(ecu_tel,:ecu_name)>0)");
		}
		if(StringUtil.isNotEmpty(begin)){
			sql.append(" AND ecu_enddate BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY ecu_id DESC");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_Ecoupon_User.class));
	}

	//在查询的时候就验证优惠券的状态能不能使用，结合临时表，计算
	@Override
	public List<T_Sell_Ecoupon_User> queryByCode(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = param.get(CommonUtil.SHOP_UPTYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecu_id,ec_use_mode as use_model,ecu_check_no,ecu_code,ecu_ec_number,ecu_money,ecu_begindate,ecu_enddate,ecu_limitmoney,");
		sql.append(" GROUP_CONCAT(ecb_bd_code) bd_code,GROUP_CONCAT(ect_tp_code) tp_code,");
		sql.append(" GROUP_CONCAT(ecp_pd_code) pd_code");
		sql.append(" FROM t_sell_ecoupon_user t");
		sql.append(" JOIN t_base_shop sp ON sp_code = ecu_shop_code AND sp.companyid = t.companyid");
		if(!CommonUtil.FOUR.equals(shop_type)){ // 如果是自营店，则下上级店铺相同所有会员
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" JOIN t_sell_ecoupon e");
		sql.append(" ON e.ec_number=ecu_ec_number");
		sql.append(" AND e.companyid=t.companyid");
		sql.append(" LEFT JOIN t_sell_ecoupon_brand b");
		sql.append(" ON b.ecb_ec_number=t.ecu_ec_number");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" LEFT JOIN t_sell_ecoupon_type p");
		sql.append(" ON p.ect_ec_number=t.ecu_ec_number");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" LEFT JOIN t_sell_ecoupon_product d");
		sql.append(" ON d.ecp_ec_number=t.ecu_ec_number");
		sql.append(" AND d.companyid=t.companyid");
		sql.append(" WHERE ecu_tel=:ecu_tel");
		sql.append(" AND ecu_state=0");
		sql.append(" AND SYSDATE() BETWEEN ecu_begindate AND ecu_enddate");
		sql.append(" AND t.companyid=:companyid");
		List<T_Sell_Ecoupon_User> list = null;
		try {
			list = namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Sell_Ecoupon_User.class));
		} catch (Exception e) {
		}
		return list;
	}

	@Override
	public void updateEcoupon(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sell_ecoupon_user");
		sql.append(" SET ecu_state=1");
		sql.append(" ,ecu_use_type=1");
		sql.append(" ,ecu_use_number=:number");
		sql.append(" ,ecu_use_date=:today");
		sql.append(" WHERE 1=1"); 
		sql.append(" AND ecu_id=:ecu_id");
		param.put("today", DateUtil.getYearMonthDate());
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}
	/**
	 * 查询全场的优惠券明细，打出符合的记录 
	 * */
	@Override
	public T_Sell_Ecoupon_User allEcoupon(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecl_number ecu_ec_number,ecl_code ecu_code,ecl_money as ecu_money,ecl_limitmoney ecu_limitmoney");
		sql.append(" FROM t_sell_ecouponlist t");
		sql.append(" WHERE ecl_number=:ec_number");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" AND :sell_money >= ecl_usedmoney");
		sql.append(" ORDER BY ecl_usedmoney DESC");
		sql.append(" LIMIT 1");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Sell_Ecoupon_User.class));
	}
	@Override
	public List<T_Sell_ECouponList> brandEcoupon(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT GROUP_CONCAT(ecb_bd_code) temp_code,ecl_usedmoney,ecl_money ecu_money,ecl_limitmoney ecu_limitmoney,ecl_number ecu_ec_number,ecl_code ecu_code");
		sql.append(" FROM t_sell_ecouponlist t");
		sql.append(" JOIN t_sell_ecoupon_brand b");
		sql.append(" ON b.ecb_ec_number=t.ecl_number");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND b.ecb_type=1");
		sql.append(" AND t.ecl_number=:ec_number");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY ecl_code");
		List<T_Sell_ECouponList> list = null;
		try {
			
			list = namedParameterJdbcTemplate.query(sql.toString(), param, 
					new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
		} catch (Exception e) {
		}
		return list;
	}
	@Override
	public List<T_Sell_ECouponList> typeEcoupon(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT GROUP_CONCAT(ecb_tp_code) temp_code,ecl_usedmoney,ecl_usedmoney,ecl_money ecu_money,ecl_limitmoney ecu_limitmoney,ecl_number ecu_ec_number,ecl_code ecu_code");
		sql.append(" FROM t_sell_ecouponlist t");
		sql.append(" JOIN t_sell_ecoupon_type b");
		sql.append(" ON b.ect_ec_number=t.ecl_number");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND b.ect_type=1");
		sql.append(" AND t.ecl_number=:ec_number");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY ecl_code");
		List<T_Sell_ECouponList> list = null;
		try {
			
			list = namedParameterJdbcTemplate.query(sql.toString(), param, 
					new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
		} catch (Exception e) {
		}
		return list;
	}
	@Override
	public List<T_Sell_ECouponList> productEcoupon(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT GROUP_CONCAT(ecb_pd_code) temp_code,ecl_usedmoney,ecl_usedmoney,ecl_money ecu_money,ecl_limitmoney ecu_limitmoney,ecl_number ecu_ec_number,ecl_code ecu_code");
		sql.append(" FROM t_sell_ecouponlist t");
		sql.append(" JOIN t_sell_ecoupon_product b");
		sql.append(" ON b.ecp_ec_number=t.ecl_number");
		sql.append(" AND b.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND b.ecp_type=1");
		sql.append(" AND t.ecl_number=:ec_number");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY ecl_code");
		List<T_Sell_ECouponList> list = null;
		try {
			
			list = namedParameterJdbcTemplate.query(sql.toString(), param, 
					new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
		} catch (Exception e) {
		}
		return list;
	}

	@Override
	public void save(T_Sell_Ecoupon_User user) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sell_ecoupon_user(");
		sql.append("ecu_ec_number,ecu_code,ecu_sh_number,ecu_date,");
		sql.append("ecu_name,ecu_tel,ecu_vip_code,ecu_money,");
		sql.append("ecu_limitmoney,ecu_shop_code,ecu_begindate,");
		sql.append("ecu_enddate,ecu_state,ecu_check_no,companyid");
		sql.append(") VALUES (");
		sql.append(":ecu_ec_number,:ecu_code,:ecu_sh_number,:ecu_date,");
		sql.append(":ecu_name,:ecu_tel,:ecu_vip_code,:ecu_money,");
		sql.append(":ecu_limitmoney,:ecu_shop_code,:ecu_begindate,");
		sql.append(":ecu_enddate,:ecu_state,:ecu_check_no,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(),
				new BeanPropertySqlParameterSource(user));
	}

	@Override
	public List<T_Sell_ECoupon> queryEcoupon(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ec_number,ec_mode,ec_deadline,t.companyid");
		sql.append(" FROM t_sell_ecoupon t");
		sql.append(" JOIN t_sell_ecoupon_shop s");
		sql.append(" ON s.ecs_ec_number=t.ec_number");
		sql.append(" AND s.companyid=t.companyid");
		sql.append(" AND s.ecs_shop_code=:shop_code");
		sql.append(" WHERE 1=1");
		sql.append(" AND SYSDATE() BETWEEN ec_begindate AND ec_enddate");
		sql.append(" AND ec_type=1");
		sql.append(" AND ec_state=1");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY ec_level");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Sell_ECoupon.class));
	}
	@Override
	public List<SellECouponDto> listEcoupon4Push(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT * FROM(");
		sql.append(" SELECT ec_id,ec_number,ec_name,ec_manager,ec_begindate,ec_enddate,t.companyid,ecl_id,ecl_code,ecl_limitmoney,ecl_money,ecl_amount,");
		sql.append(" (SELECT COUNT(1) FROM t_sell_ecoupon_user ecu WHERE ecu_code = ecl_code AND ecu.companyid = t.companyid) AS receive_amount");
		sql.append(" FROM t_sell_ecoupon t");
		sql.append(" JOIN t_sell_ecouponlist ecl ON ecl_number = ec_number AND ecl.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" WHERE 1=1");
			sql.append(" AND ec_shop_code = :shop_code ");
		}else{//自营、加盟、合伙
			sql.append(" JOIN t_sell_ecoupon_shop ecs ON ecs_ec_number = ec_number AND ecs.companyid = t.companyid");
			sql.append(" WHERE 1=1");
			sql.append(" AND ecs_shop_code = :shop_code ");
		}
		sql.append(" AND ec_type = 0");
		sql.append(" AND ec_state = 1");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" )temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND receive_amount < ecl_amount");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else {
			sql.append(" ORDER BY ec_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SellECouponDto.class));
	}
	
	@Override
	public T_Sell_ECouponList loadDetail(String ecl_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ecl_id,ecl_number,ecl_code,ecl_amount");
		sql.append(" FROM t_sell_ecouponlist t ");
		sql.append(" WHERE 1=1");
		sql.append(" AND ecl_code = :ecl_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("ecl_code", ecl_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sell_ECouponList.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public Integer countECouponReceived(String ecu_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sell_ecoupon_user t");
		sql.append(" WHERE ecu_code = :ecu_code");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("ecu_code", ecu_code).addValue("companyid", companyid),Integer.class);
	}
	

}
